from django.shortcuts import render
from django.views import View
from django import http
from django.db.models import F, Q, Avg, Sum, Count, Min, Max

from booktest.models import BookInfo, HeroInfo


# Create your views here.
class AddModelView(View):
    """测试增加数据
    GET http://127.0.0.1:8000/add/
    """

    def get(self, request):
        """
        使用save()方法增加数据
        save()方法最根本的含义是：将模型对象属性中的值，同步到对应数据表的字段中！！！
        """
        # 使用模型类初始化模型对象
        book = BookInfo()
        # 面向对象方式给属性赋值
        book.btitle = "西游记"
        book.bpub_date = "1995-2-1"
        book.bcomment = 30
        book.bread = 200
        # 区别于SQl，book.is_delete默认值是False，新增时可以不用写！！！！
        book.save()
        return http.HttpResponse("使用save()方法添加数据成功！")

    def post(self, request):
        """
        使用create()放法新增语法：模型类.模型管理器.create(模型属性=值)
        模型管理器：是由Django提供并封装的一个对象（objects），用于调用ORM的接口方法，固定的语法
        """
        BookInfo.objects.create(
            btitle = "金瓶梅",
            bpub_date = "2020-2-1",
            bcomment = 1000,
            bread = 500
        )
        return http.HttpResponse("使用create()方法添加数据成功！")


class ModifyModelView(View):

    def get(self, request):
        """使用save()方法修改数据"""
        book = BookInfo.objects.get(id = 6)
        book.btitle = "三国演义"
        book.save()
        return http.HttpResponse("使用save()方式修改数据")

    def post(self, request):
        """
        使用update()放大更新数据
        语法：模型类.模型管理器(object).filter(条件).update(...)
        """
        BookInfo.objects.filter(id = 5).update(btitle = '西游记后转')
        return http.HttpResponse("使用update()方法修改数据")


class DeleteModelView(View):

    def get(self, request):
        """语法：模型类.objects.filter('条件').delete()"""
        BookInfo.objects.filter(id=6).delete()
        return http.HttpResponse("物理删除成功！")

    def post(self, request):
        BookInfo.objects.filter(id = 5).update(is_delete=True)
        return http.HttpResponse("逻辑物理删除成功！")


class QueryOneModelView(View):
    """
    用get方法查询的时候，查询不到内容的时候会抛出异常，同样查询结果多余1条的时候也会抛出异常
    Exp：fresh.models.DoesNotExist: Cart matching query does not exist.
    filer若是查询不到数据，会返回一个空的查询集，[]  type类型是：Queryset。查询到多余一条的时候会返回一个包含多个对象的查询集。
    """
    def get(self, request):
        book = BookInfo.objects.get(id=1)
        print(book)
        return http.HttpResponse("object.get查询单张表一条数据")

    def post(self, request):
        book = BookInfo.objects.filter(id=2)
        print(book)
        return http.HttpResponse("object.filter查询单张表一条数据")


class QueryAllModelView(View):

    def get(self, request):
        books = BookInfo.objects.all()
        for book in books:
            print(book.btitle)
        return http.HttpResponse("object.all()方法查询单表所有")

    def post(self, request):
        books = BookInfo.objects.filter(is_delete=False).all()
        for book in books:
            print(book.btitle)
        return http.HttpResponse("查询单表所有标记为没有删除的书籍")


class QureyExactView(View):
    """
    精确匹配objects.filter(属性__exact = 值),其中__exact可省略,直接接"="即可
    """
    def get(self, request):
        book = BookInfo.objects.filter(btitle__exact="西游记后转")
        print(book)
        return http.HttpResponse("__exact精确匹配搜索")

    def post(self, request):
        book = BookInfo.objects.filter(btitle="雪山飞狐")
        print(book)
        return http.HttpResponse("精确匹配搜索")


class QueryContainView(View):

    def get(self, request):
        books = BookInfo.objects.filter(btitle__contains='湖')
        print(books)
        return http.HttpResponse("模糊查询-包含")


class QueryLikeView(View):

    def get(self, request):
        """测试模糊匹配endwith"""
        books = BookInfo.objects.filter(btitle__endswith="传")
        print(books)
        return http.HttpResponse("模糊查询之endwith")

    def post(self, request):
        """测试模糊匹配startwith"""
        books = BookInfo.objects.filter(btitle__startswith="射")
        print(books)
        return http.HttpResponse("模糊查询之startwith")


class QueryIsNullView(View):

    def get(self, request):
        # 查询书名不为空的部分
        books = BookInfo.objects.filter(btitle__isnull=False)
        print(books)
        return http.HttpResponse("查询不为空")


class QueryRangeView(View):
    # .filter(属性__in=[范围])
    def get(self, request):
        books = BookInfo.objects.filter(id__in=[2-4])
        print(books)
        return http.HttpResponse("范围查询")


class QureyGreateView(View):

    def get(self, request):
        books = BookInfo.objects.filter(bcomment__gt=30)
        print(books)
        return http.HttpResponse("条件大于查询:评论大于20")

    def post(self, request):
        books = BookInfo.objects.filter(bcomment__gte=20)
        print(books)
        return http.HttpResponse("条件大于等于查询:评论大于等于20")


class QueryLessView(View):

    def get(self, request):
        books = BookInfo.objects.filter(bpub_date__lt="1995-12-24")
        print(books)
        return http.HttpResponse("条件小于查询:发布时间早于1995-12-24")

    def post(self, request):
        books = BookInfo.objects.filter(bpub_date__lte="1995-12-24")
        print(books)
        return http.HttpResponse("条件小于等于查询:发布时间早于等于1995-12-24")


class QueryExcludeView(View):

    def get(self, request):
        # 表示不等于的方法1:exclude()
        books = BookInfo.objects.exclude(id=3)
        print(books)
        return http.HttpResponse("exclude不等于")

    def post(self, request):
        # 表示不等于的方法2:~Q(条件)
        books = BookInfo.objects.filter(~Q(id=3))
        print(books)
        return http.HttpResponse("~Q(条件)不等于")


class QueryFView(View):
    """
    两个属性的比较使用F对象
    需导包from django.db.models import F
    同时可以在F对象上使用算数运算
    """
    def get(self, request):
        books = BookInfo.objects.filter(bread__gt=F('bcomment'))
        print(books)
        return http.HttpResponse("F对象查询:阅读量大于评论量的书籍")

    def post(self, request):
        books = BookInfo.objects.filter(bread__gt=F('bcomment') * 3)
        print(books)
        return http.HttpResponse("F对象查询:阅读量大于三倍评论量的书籍")


class QueryAggregateView(View):
    """
    Avg 求平均值、Count 求数量、Max 求最大值、Min 求最小值、Sum 求和
    导包:from django.db.models import Sum,Max,Count,Min
    注意除了Count的聚合函数的语法:BookInfo.objects.aggregate(聚合函数(属性)),返回一个字典:{'属性名__聚合类小写':值}
    Count聚合函数语法:BookInfo.object.count(),返回一个数字
    """
    def get(self, request):
        book_dict = BookInfo.objects.aggregate(Avg("bread"))
        print(book_dict, type(book_dict))
        # 注意是连续的两个下划线
        avg = book_dict.get("bread__avg")
        print("avg:", avg)
        return http.HttpResponse("聚合函数演示:求阅读量的平均值")

    def post(self, request):
        count = BookInfo.objects.count()
        print(count, type(count))
        return http.HttpResponse("聚合函数演示:求图书总量")


class QueryOrderView(View):
    """
    升序(ASC默认)语法:模型类.objects.filter('条件').order_by('模型属性')
    降序(DESC)语法:模型类.objects.filter('条件').order_by('-模型属性')
    """
    def get(self, request):
        books = BookInfo.objects.filter(is_delete=False).order_by("id")
        print(books)
        return http.HttpResponse("升序(默认)")

    def post(self, request):
        books = BookInfo.objects.filter(is_delete=False).order_by("-id")
        print(books)
        return http.HttpResponse("降序")


class QueryInnerJoinView(View):
    # 一查多
    def get(self, request):
        # 查询出一方模型对象,只能用get获取不能使用filter
        book = BookInfo.objects.get(id=1)
        # 固定语法:在使用一方模型对象调用关联的多方模型类名小写_set.all(),一定要注意类名全小写
        heros = book.heroinfo_set.all()
        print(heros)
        return http.HttpResponse("关联查询:一查多")

    def post(self, request):
        # 查询出多方模型对象,只能用get获取不能使用filter
        hero = HeroInfo.objects.get(id=1)
        # 固定语法:在使用多方模型对象调用多方模型类中的关联的外键属性名
        books = hero.hbook
        print(books)
        return http.HttpResponse("关联查询:多查一")




